﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class TemplateRepository : BaseRepository<Template>
    {
        protected override string TableName
        {
            get { return "[Template]"; }
        }

        public override IEnumerable<Template> FindAll()
        {
            string strSQL = @"  select t.TemplateId, t.FileName, t.IsEmail, 
                    convert(bit, case when exists(select 1 from contract (nolock) where contract.TemplateId = t.TemplateId) then 0 else 1 end) as AllowDelete
                    from [Template] (nolock) t";
            return Connection.Query<Template>(strSQL);
        }

        public override Template Find(int id)
        {
            return Connection.Query<Template>(
                @"  SELECT t.TemplateId, t.FileName, t.IsEmail, convert(bit, 1) as AllowDelete
                    FROM [Template] (nolock) t
                    WHERE t.TemplateId = @Id", new { Id = id }).SingleOrDefault();
        }

        public Template Find(string fileName)
        {
            return Connection.Query<Template>(
                @"  SELECT t.TemplateId, t.FileName, t.IsEmail, convert(bit, 1) as AllowDelete
                    FROM [Template] (nolock) t
                    WHERE t.FileName = @FileName", new { FileName = fileName }).SingleOrDefault();
        }

        public Template FindEmailTemplate()
        {
            return Connection.Query<Template>(
                @"  SELECT t.TemplateId, t.FileName, t.IsEmail
                    FROM [Template] (nolock) t
                    WHERE t.IsEmail = 1").SingleOrDefault();
        }

        public void Insert(Template temp, string fieldName)
        {
            Connection.Execute(
                @"spTemplate_Add @FileName, @IsEmail, @FieldName", new{ FileName = temp.FileName, IsEmail = temp.IsEmail, FieldName = fieldName });
        }

        public new string Delete(int id)
        {
            return Connection.Query<string>("spTemplate_Delete @TemplateId", new { TemplateId = id }).Single();
        }
    }
}
